CREATE TABLE [dbo].[GroupMemberDetail]
(
[GroupMemberKey] [uniqueidentifier] NOT NULL CONSTRAINT [DF_GroupMemberDetail_GroupMemberKey] DEFAULT (newid()),
[GroupRoleKey] [uniqueidentifier] NOT NULL,
[EffectiveDate] [datetime] NOT NULL,
[ExpirationDate] [datetime] NULL,
[TypeSpecificData] [nvarchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[TypeSpecificKey] [uniqueidentifier] NULL,
[TypeSpecificFlag] [bit] NULL,
[CreatedOn] [datetime] NOT NULL CONSTRAINT [DF_GroupMemberDetail_CreatedOn] DEFAULT (getdate()),
[CreatedByUserKey] [uniqueidentifier] NOT NULL,
[UpdatedOn] [datetime] NOT NULL CONSTRAINT [DF_GroupMemberDetail_UpdatedOn] DEFAULT (getdate()),
[UpdatedByUserKey] [uniqueidentifier] NOT NULL,
[TypeSpecificInt] [int] NULL,
[GroupMemberStatusCode] [nvarchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[IsActive] [bit] NOT NULL CONSTRAINT [DF_GroupMemberDetail_IsActive] DEFAULT ((1)),
[GroupMemberDetailKey] [uniqueidentifier] NOT NULL CONSTRAINT [DF_GroupMemberDetail_GroupMemberDetailKey] DEFAULT (newid()),
[GroupKey] [uniqueidentifier] NOT NULL,
[MarkedForDeleteOn] [datetime] NULL
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[GroupMemberDetail] ADD CONSTRAINT [PK_GroupMemberDetail] PRIMARY KEY CLUSTERED ([GroupMemberDetailKey]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[GroupMemberDetail] ADD CONSTRAINT [AK_GroupMemberRoleDate] UNIQUE NONCLUSTERED ([GroupMemberKey], [GroupRoleKey], [EffectiveDate]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_GroupMemberDetail_CreatedByUserKey] ON [dbo].[GroupMemberDetail] ([CreatedByUserKey]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_GroupMemberDetail_EffectiveDate] ON [dbo].[GroupMemberDetail] ([EffectiveDate]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [AK_GroupMemberDetail_GroupKey] ON [dbo].[GroupMemberDetail] ([GroupKey]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_GroupMemberDetail_GroupMemberKey] ON [dbo].[GroupMemberDetail] ([GroupMemberKey]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_GroupMemberDetail_GroupMemberStatusCode] ON [dbo].[GroupMemberDetail] ([GroupMemberStatusCode]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_GroupMemberDetail_GroupRoleKey] ON [dbo].[GroupMemberDetail] ([GroupRoleKey]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_GroupMemberDetail_TypeSpecificKey] ON [dbo].[GroupMemberDetail] ([TypeSpecificKey]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_GroupMemberDetail_UpdatedByUserKey] ON [dbo].[GroupMemberDetail] ([UpdatedByUserKey]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[GroupMemberDetail] ADD CONSTRAINT [FK_GroupMemberDetail_GroupMain] FOREIGN KEY ([GroupKey]) REFERENCES [dbo].[GroupMain] ([GroupKey])
GO
ALTER TABLE [dbo].[GroupMemberDetail] ADD CONSTRAINT [FK_GroupMemberDetail_GroupMember] FOREIGN KEY ([GroupMemberKey]) REFERENCES [dbo].[GroupMember] ([GroupMemberKey])
GO
ALTER TABLE [dbo].[GroupMemberDetail] ADD CONSTRAINT [FK_GroupMemberDetail_GroupMemberStatusRef] FOREIGN KEY ([GroupMemberStatusCode]) REFERENCES [dbo].[GroupMemberStatusRef] ([GroupMemberStatusCode])
GO
ALTER TABLE [dbo].[GroupMemberDetail] ADD CONSTRAINT [FK_GroupMemberDetail_GroupRoleRef] FOREIGN KEY ([GroupRoleKey]) REFERENCES [dbo].[GroupRoleRef] ([GroupRoleKey])
GO
ALTER TABLE [dbo].[GroupMemberDetail] ADD CONSTRAINT [FK_GroupMemberDetail_UserMain_CreatedBy] FOREIGN KEY ([CreatedByUserKey]) REFERENCES [dbo].[UserMain] ([UserKey])
GO
ALTER TABLE [dbo].[GroupMemberDetail] ADD CONSTRAINT [FK_GroupMemberDetail_UserMain_UpdatedBy] FOREIGN KEY ([UpdatedByUserKey]) REFERENCES [dbo].[UserMain] ([UserKey])
GO